﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class HisActivityDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public HisActivityDAL()
        {

        }
        public DataSet LoadHisActivity(Nullable<int> Sequence, Nullable<int> EmpSequence, Nullable<int> BgYear)
        {
            string strSQL;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(2, "@BgYear", BgYear);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@ApproveRecord", SystemInfo._ApproveRecordStatus);

                strSQL = "SELECT r.*"
                        + ", act.ACT_DESCR, act.DATE_ACTIVITY_START, act.DATE_ACTIVITY_END"
                        + ", CONCAT(act.LOCATION_TEXT, ' ', ci.CITY_NAME, ' ', co.COUNTRY_NAME) AS LOCATION"
                        + ", CASE WHEN YEAR(act.DATE_ACTIVITY_START) = YEAR(act.DATE_ACTIVITY_END) THEN"
                            + " CASE WHEN MONTH(act.DATE_ACTIVITY_START) = MONTH(act.DATE_ACTIVITY_END) THEN"
					            + " CONCAT(CAST(DATEPART(d, act.DATE_ACTIVITY_START)AS CHAR)"
                                + ", ' - ', CAST(DATEPART( d , act.DATE_ACTIVITY_END)AS CHAR)"
                                + ", ' ', CAST(dbo.F_FORMAT_MONTH_THAI(act.DATE_ACTIVITY_START)AS CHAR)"
                                + ", ' ', YEAR(act.DATE_ACTIVITY_START))"
                            + " ELSE CONCAT(CAST(DATEPART( d ,  act.DATE_ACTIVITY_START)AS CHAR)"
                                + ", '  ', CAST(dbo.F_FORMAT_MONTH_THAI( act.DATE_ACTIVITY_START)AS CHAR)"
                                + ", ' - ', CAST(DATEPART( d ,  act.DATE_ACTIVITY_END )AS CHAR)"
                                + ", '  ', CAST(dbo.F_FORMAT_MONTH_THAI( act.DATE_ACTIVITY_END)AS CHAR)  )"
				                + " END"
                        + " ELSE CONCAT(DATEPART( d , act.DATE_ACTIVITY_START)"
                                + ", ' ', dbo.F_FORMAT_MONTH_THAI(act.DATE_ACTIVITY_START)"
                                + ", ' ', YEAR(act.DATE_ACTIVITY_START)"
                                + ", ' - ', DATEPART( d , act.DATE_ACTIVITY_END)"
                                + ", ' ', dbo.F_FORMAT_MONTH_THAI(act.DATE_ACTIVITY_END)"
                                + ", ' ', YEAR(act.DATE_ACTIVITY_END)"
                                + ")"
                        + " END AS DURATION_DATE"
                    + " FROM HRM_AC_ACTIVITY_REQUESTS r"
                    + " LEFT JOIN HRM_AC_ACTIVITYS act ON r.ACT_SEQ = act.ACT_SEQ"
                    + " LEFT JOIN MAS_CITIES ci ON act.CITY_SEQ = ci.CITY_SEQ"
                    + " LEFT JOIN MAS_COUNTRIES co ON ci.COUNTRY_SEQ = co.COUNTRY_SEQ"
                    + " WHERE r.RECORD_STATUS = @RecordStatus"
                    + " AND r.ACT_REQUEST_STATUS = @ApproveRecord"
                    + " AND r.GRANTED not like 0"
                    + (Sequence.HasValue ? " AND r.ACT_REQUEST_SEQ = @Sequence" : "")
                    + (EmpSequence.HasValue ? " AND r.EMP_SEQ = @EmpSequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
    }
}
